package limousine.dbservice;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;
import limousine.vo.Users;
import limousine.util.Func;


public class UsersService {

    private DataSource ds = null;
    private Func func = new Func();

    public UsersService() {
        //ds = (new Service()).getDataSource();
    }

    public void setDataSource(DataSource ds) {
        this.ds = ds;
    }

////////////////////////////////////////////GET
    public Users getById(String id) {//have one
        StringBuffer sql = new StringBuffer();
        sql.append("Select * From users Where id = '" + id.trim() + "' ");
        Users o = null;
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            if (rs.next()) {
                o = new Users();
                o.setId(rs.getString("ID"));
                o.setUserName(rs.getString("USER_NAME"));
                o.setPassword(rs.getString("PASSWORD"));
                o.setName(rs.getString("NAME"));
                o.setEmail(rs.getString("EMAIL"));
                o.setPhone(rs.getString("PHONE"));
                o.setGender(rs.getString("GENDER"));
                o.setCreateDate(rs.getString("CREATE_DATE"));
                o.setLastLogin(rs.getString("LAST_LOGIN"));
                o.setEnable(rs.getString("ENABLE"));
                o.setRoleId(rs.getString("ROLE_ID"));
                o.setStatus(rs.getString("STATUS"));
                o.setPreviewImage(rs.getString("PREVIEW_IMAGE"));
                o.setCreateBy(rs.getString("CREATE_BY"));
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return o;
    }

    public Users getDisplayProfileById(String id) {//have one
        StringBuffer sql = new StringBuffer();
        sql.append("Select * From users Where id = '" + id.trim() + "' ");
        Users o = null;
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            if (rs.next()) {
                o = new Users();
                o.setId(rs.getString("ID"));
                o.setUserName(rs.getString("USER_NAME"));
                o.setPassword(rs.getString("PASSWORD"));
                o.setName(rs.getString("NAME"));
                o.setEmail(rs.getString("EMAIL"));
                o.setPhone(rs.getString("PHONE"));
                o.setGender(rs.getString("GENDER"));
                o.setCreateDate(rs.getString("CREATE_DATE"));
                o.setLastLogin(rs.getString("LAST_LOGIN"));
                o.setEnable(rs.getString("ENABLE"));
                o.setRoleId(rs.getString("ROLE_ID"));
                o.setStatus(rs.getString("STATUS"));
                o.setPreviewImage(rs.getString("PREVIEW_IMAGE"));
                o.setCreateBy(rs.getString("CREATE_BY"));

                if (o.getPreviewImage().length() <= 0) {
                    if (o.getGender().toUpperCase().equals("M")) {
                        o.setPreviewImage("images/male.jpg");
                    } else {
                        o.setGender("หญิง");
                        o.setPreviewImage("images/female.jpg");
                    }
                }
                if (o.getGender().toUpperCase().equals("M")) {
                    o.setGender("ชาย");
                } else {
                    o.setGender("หญิง");
                }
                o.setCreateDate(func.convertDateTimeToDisplayTh3(o.getCreateDate()));

            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return o;
    }

    public Users getUserNamePreviewImageById(String id) {//have one
        StringBuffer sql = new StringBuffer();
        sql.append("Select user_name,preview_image,gender From users Where id = '" + id.trim() + "' ");
        Users o = null;
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            if (rs.next()) {
                o = new Users();
                o.setId(id);
                o.setUserName(rs.getString("USER_NAME"));
                o.setPreviewImage(rs.getString("PREVIEW_IMAGE"));
                if (o.getPreviewImage().length() <= 0) {
                    String gender = rs.getString("GENDER");
                    if (gender.toUpperCase().equals("M")) {
                        o.setPreviewImage("images/male.jpg");
                    } else {
                        o.setPreviewImage("images/female.jpg");
                    }
                }
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return o;
    }

    public boolean checkPasswordByById(String id, String password) {//have one
        boolean result = false;
        StringBuffer sql = new StringBuffer();
        sql.append("Select password From users Where id = '" + id.trim() + "' and password=MD5('" + password.trim() + "') ");
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            if (rs.next()) {
                result = true;
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public Users getByUserName(String user_name) {//have one
        StringBuffer sql = new StringBuffer();
        sql.append("Select * From users Where user_name = '" + user_name.trim() + "' ");
        Users o = null;
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            if (rs.next()) {
                o = new Users();
                o.setId(rs.getString("ID"));
                o.setUserName(rs.getString("USER_NAME"));
                o.setPassword(rs.getString("PASSWORD"));
                o.setName(rs.getString("NAME"));
                o.setEmail(rs.getString("EMAIL"));
                o.setPhone(rs.getString("PHONE"));
                o.setGender(rs.getString("GENDER"));
                o.setCreateDate(rs.getString("CREATE_DATE"));
                o.setLastLogin(rs.getString("LAST_LOGIN"));
                o.setEnable(rs.getString("ENABLE"));
                o.setRoleId(rs.getString("ROLE_ID"));
                o.setStatus(rs.getString("STATUS"));
                o.setPreviewImage(rs.getString("PREVIEW_IMAGE"));
                o.setCreateBy(rs.getString("CREATE_BY"));
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return o;
    }

    public List<Users> getAll() {
        StringBuffer sql = new StringBuffer();
        sql.append("Select * From users order by user_name");
        List<Users> l = new ArrayList<Users>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            while (rs.next()) {
                Users o = new Users();
                o.setId(rs.getString("ID"));
                o.setUserName(rs.getString("USER_NAME"));
                o.setPassword(rs.getString("PASSWORD"));
                o.setName(rs.getString("NAME"));
                o.setEmail(rs.getString("EMAIL"));
                o.setPhone(rs.getString("PHONE"));
                o.setGender(rs.getString("GENDER"));
                o.setCreateDate(rs.getString("CREATE_DATE"));
                o.setLastLogin(rs.getString("LAST_LOGIN"));
                o.setEnable(rs.getString("ENABLE"));
                o.setRoleId(rs.getString("ROLE_ID"));
                o.setStatus(rs.getString("STATUS"));
                o.setPreviewImage(rs.getString("PREVIEW_IMAGE"));
                o.setCreateBy(rs.getString("CREATE_BY"));
                l.add(o);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return l;
    }

    public List<Users> getByRoleId(String role_id) {//have one
        StringBuffer sql = new StringBuffer();
        sql.append("Select * From users Where role_id = '" + role_id.trim() + "' ");
        List<Users> list = new ArrayList<Users>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            while (rs.next()) {
                Users o = new Users();
                o.setId(rs.getString("ID"));
                o.setUserName(rs.getString("USER_NAME"));
                o.setPassword(rs.getString("PASSWORD"));
                o.setName(rs.getString("NAME"));
                o.setEmail(rs.getString("EMAIL"));
                o.setPhone(rs.getString("PHONE"));
                o.setGender(rs.getString("GENDER"));
                o.setCreateDate(rs.getString("CREATE_DATE"));
                o.setLastLogin(rs.getString("LAST_LOGIN"));
                o.setEnable(rs.getString("ENABLE"));
                o.setRoleId(rs.getString("ROLE_ID"));
                o.setStatus(rs.getString("STATUS"));
                o.setPreviewImage(rs.getString("PREVIEW_IMAGE"));
                o.setCreateBy(rs.getString("CREATE_BY"));
                list.add(o);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public boolean checkUsernameExist(String username) {
        StringBuffer sql = new StringBuffer();
        sql.append("select * from users where UPPER(user_name) = '" + username.trim().toUpperCase() + "' ");

        boolean exist = false;
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            if (rs.next()) {
                exist = true;
            }
            stmt.close();
            rs.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return exist;
    }
////////////////////////////////////////////GET

//////////////////////////////////INSERT
    public boolean insert(Users o) {
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO users(id,user_name,password,name,email,phone,gender,create_date,last_login,enable,role_id,status,preview_image,create_by) ");
        sql.append("VALUES (id,?,MD5(?),?,?,?,?,?,?,?,?,?,?,?)");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, o.getUserName());
            stmt.setString(2, o.getPassword());
            stmt.setString(3, o.getName());
            stmt.setString(4, o.getEmail());
            stmt.setString(5, o.getPhone());
            stmt.setString(6, o.getGender());
            stmt.setTimestamp(7, func.getTimestamp());
            stmt.setTimestamp(8,null);
            stmt.setString(9, o.getEnable());
            stmt.setString(10, o.getRoleId());
            stmt.setString(11, o.getStatus());
            stmt.setString(12, o.getPreviewImage());
            stmt.setString(13, o.getCreateBy());
            
            stmt.executeUpdate();
            stmt.close();
            //conn.commit();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }
////////////////////////////////// INSERT

///////////////////////////////// UPDATE
    public boolean update(Users o) {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE users ");
        sql.append("SET user_name=?,password=?,name=?,email=?,phone=?,gender=?,enable=?,role_id=? ");
        sql.append("where id='" + o.getId() + "' ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, o.getUserName());
            stmt.setString(2, o.getPassword());
            stmt.setString(3, o.getName());
            stmt.setString(4, o.getEmail());
            stmt.setString(5, o.getPhone());
            stmt.setString(6, o.getGender());
            stmt.setString(7, o.getEnable());
            stmt.setString(8, o.getRoleId());

            stmt.executeUpdate();
            stmt.close();
            //conn.commit();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean updateUserInfomation(Users o) {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE users ");
        sql.append("SET password=MD5(?),name=?,email=?,phone=? ");
        sql.append("where id='" + o.getId() + "' ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, o.getPassword());
            stmt.setString(2, o.getName());
            stmt.setString(3, o.getEmail());
            stmt.setString(4, o.getPhone());
            stmt.executeUpdate();
            stmt.close();
            //conn.commit();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean updateProfile(Users o) {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE users ");
        sql.append("SET name=?,email=?,phone=?,gender=?,preview_image=? ");
        sql.append("where id='" + o.getId() + "' ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, o.getName());
            stmt.setString(2, o.getEmail());
            stmt.setString(3, o.getPhone());
            stmt.setString(4, o.getGender());            
            stmt.setString(5, o.getPreviewImage());
            stmt.executeUpdate();
            stmt.close();
            //conn.commit();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean updatePassword(String id, String password) {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE users SET password=MD5(?) where id=? ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, password);
            stmt.setString(2, id);
            stmt.executeUpdate();
            stmt.close();
            //conn.commit();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean updateUserInfomationNotChangePassword(Users o) {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE users ");
        sql.append("SET name=?,email=?,phone=?,gender=?,enable=?,role_id=? ");
        sql.append("where id='" + o.getId() + "' ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            //stmt.setString(1, o.getPassword());
            stmt.setString(1, o.getName());
            stmt.setString(2, o.getEmail());
            stmt.setString(3, o.getPhone());
            stmt.setString(4, o.getGender());
            stmt.setString(5, o.getEnable());
            stmt.setString(6, o.getRoleId());            
            stmt.executeUpdate();
            stmt.close();
            //conn.commit();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }
///////////////////////////////// UPDATE

//////////////////////////////////DELETE
    public boolean deleteById(String id) {//delete
        StringBuffer sql = new StringBuffer();
        sql.append("delete from users where id = '" + id.trim() + "' ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.executeUpdate();
            stmt.close();
            //conn.commit();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean deleteByRoleId(String id) {//delete
        StringBuffer sql = new StringBuffer();
        sql.append("delete from users where role_id = '" + id.trim() + "' ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.executeUpdate();
            stmt.close();
            //conn.commit();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean deleteBySQL(String sql) {//use for web
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();
            stmt.close();
            //conn.commit();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }
//////////////////////////////////DELETE
}
